package com.geek.jdbc.statement;

import com.geek.jdbc.entity.Girl;

import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * @Desc XxxDao 与数据层交换
 * @Author
 * @Date 2025/11/17
 *
 * Statement 语句对象  弊端
 * 1.SQL拼接，''进行拼接字段，麻烦且容易出错
 * 2.查询容易引发SQL注入问题  ' or 1=1 --   引发数据安全问题
 * 3.SQL语句编译次数过多，导致效率较低
 */
public class GirlDao {


    /**
     * 需求：根据姓名和手机号查询女生信息
     * @param name  姓名
     * @param phone 手机号
     * @return  查询的女生信息
     */
    public List<Girl> selectOne(String name , String phone){
        List<Girl> list = new ArrayList<>();
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            //1.加载驱动类
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.通过驱动管理器获取连接对象
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/testsqlgeek?useUnicode=true&charsetEncoding=UTF8&serverTimezone=Asia/Shanghai",
                    "root", "094115");
            //3.获取语句对象
            st = conn.createStatement();
            //4.执行语句获取结果集
            String sql = "select * from girl where name = '"+name+"' and phone = '"+phone+"'";
            rs = st.executeQuery(sql);
            //5.处理结果集
            while (rs.next()){
                Girl girl = new Girl();
                girl.setId(rs.getInt("id"));
                girl.setName(rs.getString("name"));
                girl.setSex(rs.getString("sex"));
                String borndateStr = rs.getString("borndate");
                LocalDate borndate = LocalDate.parse(borndateStr, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                girl.setBorndate(borndate);
                girl.setPhone(rs.getString("phone"));
                girl.setBoyfriendId(rs.getInt("boyfriend_id"));
                list.add(girl);
            }
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally{
            //6.关闭资源
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(st!=null){
                try {
                    st.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return list;
    }


}
